package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import util.Constant;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mchange.v2.c3p0.DataSources;

public class PooledConn
{
	private static String DBClassName = null;
	private static String DBUrl = null;
	private static String DBUser = null;
	private static String DBPassword  = null;
	
	private static ComboPooledDataSource cpds = null;;
	
	private static PooledConn instance = null;
	
	protected PooledConn() { }
	
	static
	{
		try
		{
			DBClassName = Constant.getValue("jdbc.driver").toString() ;
			DBUrl = Constant.getValue("jdbc.url").toString() ;
			DBUser = Constant.getValue("jdbc.username").toString() ;
			DBPassword = Constant.getValue("jdbc.password").toString() ;
			
			cpds = new ComboPooledDataSource();
			cpds.setDriverClass(DBClassName);
			cpds.setJdbcUrl(DBUrl);
			cpds.setUser(DBUser);
			cpds.setPassword(DBPassword);
			
			cpds.setInitialPoolSize(20);//最大空闲时间,
			cpds.setMinPoolSize(10);// 设置连接池的最小连接数 
			cpds.setAcquireIncrement(5);//定义在从数据库获取新连接失败后重复尝试的次数
			cpds.setMaxPoolSize(50);// 设置连接池的最大连接数 
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
	
	public static synchronized PooledConn getInstance()
	{
		if(instance == null)
		{
			instance = new PooledConn();
		}
		return instance;
	}
	
	public synchronized void close()
	{
		try
		{
			DataSources.destroy(cpds);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
	
	public synchronized ResultSet executeQuery(String sql)
	{
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try
		{
			conn = cpds.getConnection();
			st = conn.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = st.executeQuery(sql);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		return rs;
	}

	public synchronized boolean executeInsert(String sql)
	{
		boolean result = false;
		Connection conn = null;
		Statement st = null;
		try
		{
			conn = cpds.getConnection();
			st = conn.createStatement();
			result = st.execute(sql);
			st.close();
			conn.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		return result;
	}
	
	public synchronized int executeUpdate(String sql)
	{
		int result = 0;
		Connection conn = null;
		Statement st = null;
		try
		{
			conn = cpds.getConnection();
			st = conn.createStatement();
			result = st.executeUpdate(sql);
			st.close();
			conn.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		return result;
	}
	
	public static void main(String[] args){
		StringBuffer sql = new StringBuffer();
		sql.append("insert into a_comment(id,appId,userId,createTime,score,conntext,flag) values('123','232','4','2014-06-16 19:09:26','5','手工费','0') ");
		System.out.println(getInstance().executeInsert(sql.toString()));
	}

}
